package cn.Service.Impl;

import cn.CommonUtils.ApplicationConfigurationSingletonFactory;
import cn.Entity.ConsumerBasicInfo;
import cn.Entity.ConsumptionRecord;
import cn.Entity.MaScanPoint;
import cn.Entity.PlatinumbagRedEnvelope;
import cn.Service.GenerateReportToExcel;
import cn.Service.QueryDataByMutilDataSource;
import io.swagger.models.auth.In;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.awt.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
import java.util.stream.Collectors;
@Slf4j
@Service
public class GenerateReportToExcelImpl implements GenerateReportToExcel {
    @Autowired
    private QueryDataByMutilDataSource queryDataByMutilDataSource;

    private List<ConsumptionRecord> consumptionRecordList;
    private List<ConsumerBasicInfo> consumerBasicInfoList;
    private List<MaScanPoint> maScanPointList;

    private List<Map<String,Object>> result = new LinkedList<>();

    Integer rowIndex = 0;



    public GenerateReportToExcelImpl(){
        consumptionRecordList = new LinkedList<>();
        consumerBasicInfoList = new LinkedList<>();
        maScanPointList = new LinkedList<>();
    }

    @Override
    public synchronized void generatePlatinumBagConsumeScanCodeRecord() {
        consumptionRecordList = queryDataByMutilDataSource.listAllConsumptionRecord();
        consumerBasicInfoList = queryDataByMutilDataSource.listAllConsumerBasicInfo();
        maScanPointList = queryDataByMutilDataSource.listAllMaScanPoint();

        Iterator<ConsumptionRecord> consumptionRecordIterator = consumptionRecordList.iterator();
        while (consumptionRecordIterator.hasNext()) {
            Map<String,Object> itemTemp = new LinkedHashMap<>();
            ConsumptionRecord consumptionRecordTemp = consumptionRecordIterator.next();
            itemTemp.put("cOpenId", consumptionRecordTemp.getUserNo());
            itemTemp.put("verifyConsumeTime", consumptionRecordTemp.getVerifyConsumeTime());
            itemTemp.put("iCode", consumptionRecordTemp.getInnerCode());
            itemTemp.put("oCode", consumptionRecordTemp.getOuterCode());
            itemTemp.put("sku", consumptionRecordTemp.getSku());
            itemTemp.put("category", consumptionRecordTemp.getCategory());
            itemTemp.put("fName", consumptionRecordTemp.getFullName());

            Iterator<ConsumerBasicInfo> consumerBasicInfoIterator = consumerBasicInfoList.iterator();
            while (consumerBasicInfoIterator.hasNext()) {
                ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoIterator.next();
                if (consumptionRecordTemp.getUserNo().equals(consumerBasicInfoTemp.getOpenId())) {
                    itemTemp.put("cProvince", consumerBasicInfoTemp.getProvince());
                    itemTemp.put("cCity", consumerBasicInfoTemp.getCity());
                    consumerBasicInfoIterator.remove();
                    break;
                }
            }

            Iterator<MaScanPoint> maScanPointIterator = maScanPointList.iterator();
            while (maScanPointIterator.hasNext()) {
                MaScanPoint maScanPointTemp = maScanPointIterator.next();
                if (consumptionRecordTemp.getInnerCode().equals(maScanPointTemp.getInnerCode())) {
                    itemTemp.put("mScanCodeTime", maScanPointTemp.getScanDate());
                    itemTemp.put("mOpenId", maScanPointTemp.getOpenId());
                    itemTemp.put("mName", maScanPointTemp.getCustormername());
                    itemTemp.put("mCategory", maScanPointTemp.getIdentityCategory());
                    itemTemp.put("mPhone", maScanPointTemp.getPhone());
                    itemTemp.put("storeId", maScanPointTemp.getStoreId());
                    itemTemp.put("sName", maScanPointTemp.getStoreName());
                    itemTemp.put("mProvince", maScanPointTemp.getProvice());
                    itemTemp.put("mCity", maScanPointTemp.getCity());
                    itemTemp.put("mArea", maScanPointTemp.getArea());
                    itemTemp.put("mTownshop", maScanPointTemp.getTownship());
                    itemTemp.put("mDAddress", maScanPointTemp.getDetailaddress());
//                    itemTemp.put("mSArea", maScanPointTemp.getStorearea());

                    maScanPointIterator.remove();
                    break;
                }
            }

            /**
             * 将数据填充到result
             */

            result.add(itemTemp);

            consumptionRecordIterator.remove();

        }


        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("扫码数据");
//      读取result、放入Excel
        result.forEach(i->{
            XSSFRow row = sheet.createRow(rowIndex++);
            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(4);
            XSSFCell cell5 = row.createCell(5);
            XSSFCell cell6 = row.createCell(6);
            XSSFCell cell7 = row.createCell(7);
            XSSFCell cell8 = row.createCell(8);
            XSSFCell cell9 = row.createCell(9);
            XSSFCell cell10 = row.createCell(10);
            XSSFCell cell11 = row.createCell(11);
            XSSFCell cell12 = row.createCell(12);
            XSSFCell cell13 = row.createCell(13);
            XSSFCell cell14 = row.createCell(14);
            XSSFCell cell15 = row.createCell(15);
            XSSFCell cell16 = row.createCell(16);
            XSSFCell cell17 = row.createCell(17);
            XSSFCell cell18 = row.createCell(18);
            XSSFCell cell19 = row.createCell(19);
            XSSFCell cell20 = row.createCell(20);
            XSSFCell cell21 = row.createCell(21);

//          消费者核销数据
            cell1.setCellValue(i.get("cOpenId").toString());
            if (i.get("verifyConsumeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

                cell2.setCellValue( (sdf.format((Date) (i.get("verifyConsumeTime")))) );
            }
            cell3.setCellValue(i.get("iCode").toString());
            cell4.setCellValue(i.get("oCode").toString());
            cell5.setCellValue(i.get("sku").toString());
            cell6.setCellValue(i.get("category").toString());
            cell7.setCellValue((String) Optional.ofNullable(i.get("cProvince")).orElse("") );
            cell8.setCellValue((String) Optional.ofNullable(i.get("cCity")).orElse(""));
            cell9.setCellValue((String) i.get("fName"));

//            销售扫码数据
            if (i.get("mScanCodeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                cell10.setCellValue(sdf.format ((Date) i.get("mScanCodeTime")));
            }
            cell11.setCellValue( (String) i.get("mOpenId"));
            cell12.setCellValue( (String) i.get("mName"));
            cell13.setCellValue( (String) i.get("mCategory"));
            cell14.setCellValue( (String) i.get("mPhone"));
            if (i.get("storeId") != null) {
                cell15.setCellValue( (Integer)( i.get("storeId")) );

            }
            cell16.setCellValue( (String) i.get("sName"));
            cell17.setCellValue( (String) i.get("mProvince"));
            cell18.setCellValue( (String) i.get("mCity"));
            cell19.setCellValue( (String) i.get("mArea"));
            cell20.setCellValue( (String) i.get("mTownshop"));
            cell21.setCellValue( (String) i.get("mDAddress"));

        });

        FileOutputStream fileOutputStream = null;
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String reportDate = simpleDateFormat.format(new Date());
            fileOutputStream = new FileOutputStream("D:/tmp/PlatinumBagVerifyConsumeRecord" + reportDate + ".xlsx");

            workbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    @Override
    public synchronized void generatePlatinumBagConsumeScanCodeRecord2() {
        consumptionRecordList = queryDataByMutilDataSource.listAllConsumptionRecord();
        consumerBasicInfoList = queryDataByMutilDataSource.listAllConsumerBasicInfo();

        StringBuffer stringBuffer = new StringBuffer();

        int consumptionRecordListLen = consumptionRecordList.size() - 1;
                ;
        for (int i = 0; i < consumptionRecordList.size(); i++) {

            if (i <  consumptionRecordListLen) {
                stringBuffer.append(consumptionRecordList.get(i).getInnerCode() + ",");
            }else{
                stringBuffer.append(consumptionRecordList.get(i).getInnerCode() );
            }
        }

        maScanPointList = queryDataByMutilDataSource.listAllMaScanPointInInnerCodes(stringBuffer.toString());

        Iterator<ConsumptionRecord> consumptionRecordIterator = consumptionRecordList.iterator();
        while (consumptionRecordIterator.hasNext()) {
            Map<String,Object> itemTemp = new LinkedHashMap<>();
            ConsumptionRecord consumptionRecordTemp = consumptionRecordIterator.next();
            itemTemp.put("cOpenId", consumptionRecordTemp.getUserNo());
            itemTemp.put("verifyConsumeTime", consumptionRecordTemp.getVerifyConsumeTime());
            itemTemp.put("iCode", consumptionRecordTemp.getInnerCode());
            itemTemp.put("oCode", consumptionRecordTemp.getOuterCode());
            itemTemp.put("sku", consumptionRecordTemp.getSku());
            itemTemp.put("category", consumptionRecordTemp.getCategory());
            itemTemp.put("fName", consumptionRecordTemp.getFullName());

            Iterator<ConsumerBasicInfo> consumerBasicInfoIterator = consumerBasicInfoList.iterator();
            while (consumerBasicInfoIterator.hasNext()) {
                ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoIterator.next();
                if (consumptionRecordTemp.getUserNo().equals(consumerBasicInfoTemp.getOpenId())) {
                    itemTemp.put("cProvince", consumerBasicInfoTemp.getProvince());
                    itemTemp.put("cCity", consumerBasicInfoTemp.getCity());
                    consumerBasicInfoIterator.remove();
                    break;
                }
            }

            Iterator<MaScanPoint> maScanPointIterator = maScanPointList.iterator();
            while (maScanPointIterator.hasNext()) {
                MaScanPoint maScanPointTemp = maScanPointIterator.next();
                if (consumptionRecordTemp.getInnerCode().equals(maScanPointTemp.getInnerCode())) {
                    itemTemp.put("mScanCodeTime", maScanPointTemp.getScanDate());
                    itemTemp.put("mOpenId", maScanPointTemp.getOpenId());
                    itemTemp.put("mName", maScanPointTemp.getCustormername());
                    itemTemp.put("mCategory", maScanPointTemp.getIdentityCategory());
                    itemTemp.put("mPhone", maScanPointTemp.getPhone());
                    itemTemp.put("storeId", maScanPointTemp.getStoreId());
                    itemTemp.put("sName", maScanPointTemp.getStoreName());
                    itemTemp.put("mProvince", maScanPointTemp.getProvice());
                    itemTemp.put("mCity", maScanPointTemp.getCity());
                    itemTemp.put("mArea", maScanPointTemp.getArea());
                    itemTemp.put("mTownshop", maScanPointTemp.getTownship());
                    itemTemp.put("mDAddress", maScanPointTemp.getDetailaddress());
//                    itemTemp.put("mSArea", maScanPointTemp.getStorearea());

                    maScanPointIterator.remove();
                    break;
                }
            }

            /**
             * 将数据填充到result
             */

            result.add(itemTemp);

            consumptionRecordIterator.remove();

        }


        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("扫码数据");
//      读取result、放入Excel
        result.forEach(i->{
            XSSFRow row = sheet.createRow(rowIndex++);
            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(4);
            XSSFCell cell5 = row.createCell(5);
            XSSFCell cell6 = row.createCell(6);
            XSSFCell cell7 = row.createCell(7);
            XSSFCell cell8 = row.createCell(8);
            XSSFCell cell9 = row.createCell(9);
            XSSFCell cell10 = row.createCell(10);
            XSSFCell cell11 = row.createCell(11);
            XSSFCell cell12 = row.createCell(12);
            XSSFCell cell13 = row.createCell(13);
            XSSFCell cell14 = row.createCell(14);
            XSSFCell cell15 = row.createCell(15);
            XSSFCell cell16 = row.createCell(16);
            XSSFCell cell17 = row.createCell(17);
            XSSFCell cell18 = row.createCell(18);
            XSSFCell cell19 = row.createCell(19);
            XSSFCell cell20 = row.createCell(20);
            XSSFCell cell21 = row.createCell(21);

//          消费者核销数据
            cell1.setCellValue(i.get("cOpenId").toString());
            if (i.get("verifyConsumeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

                cell2.setCellValue( (sdf.format((Date) (i.get("verifyConsumeTime")))) );
            }
            cell3.setCellValue(i.get("iCode").toString());
            cell4.setCellValue(i.get("oCode").toString());
            cell5.setCellValue(i.get("sku").toString());
            cell6.setCellValue(i.get("category").toString());
            cell7.setCellValue((String) Optional.ofNullable(i.get("cProvince")).orElse("") );
            cell8.setCellValue((String) Optional.ofNullable(i.get("cCity")).orElse(""));
            cell9.setCellValue((String) i.get("fName"));

//            销售扫码数据
            if (i.get("mScanCodeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                cell10.setCellValue(sdf.format ((Date) i.get("mScanCodeTime")));
            }
            cell11.setCellValue( (String) i.get("mOpenId"));
            cell12.setCellValue( (String) i.get("mName"));
            cell13.setCellValue( (String) i.get("mCategory"));
            cell14.setCellValue( (String) i.get("mPhone"));
            if (i.get("storeId") != null) {
                cell15.setCellValue( (Integer)( i.get("storeId")) );

            }
            cell16.setCellValue( (String) i.get("sName"));
            cell17.setCellValue( (String) i.get("mProvince"));
            cell18.setCellValue( (String) i.get("mCity"));
            cell19.setCellValue( (String) i.get("mArea"));
            cell20.setCellValue( (String) i.get("mTownshop"));
            cell21.setCellValue( (String) i.get("mDAddress"));

        });

        FileOutputStream fileOutputStream = null;
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String reportDate = simpleDateFormat.format(new Date());
            fileOutputStream = new FileOutputStream("D:/tmp/PlatinumBagVerifyConsumeRecord" + reportDate + ".xlsx");

            workbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    @Override
    public synchronized void generatePlatinumBagConsumeScanCodeRecord3() {

        List<Map<String,Object>> result = new LinkedList<>();

        consumptionRecordList = queryDataByMutilDataSource.listAllConsumptionRecord();
        consumerBasicInfoList = queryDataByMutilDataSource.listAllConsumerBasicInfo();

        StringBuffer stringBuffer = new StringBuffer();

        int consumptionRecordListLen = consumptionRecordList.size() - 1;
        ;
        for (int i = 0; i < consumptionRecordList.size(); i++) {

            if (i <  consumptionRecordListLen) {
                stringBuffer.append(consumptionRecordList.get(i).getInnerCode() + ",");
            }else{
                stringBuffer.append(consumptionRecordList.get(i).getInnerCode() );
            }
        }

        maScanPointList = queryDataByMutilDataSource.listAllMaScanPointInInnerCodes(stringBuffer.toString());

        Iterator<ConsumptionRecord> consumptionRecordIterator = consumptionRecordList.iterator();
        while (consumptionRecordIterator.hasNext()) {
            Map<String,Object> itemTemp = new LinkedHashMap<>();
            ConsumptionRecord consumptionRecordTemp = consumptionRecordIterator.next();
            itemTemp.put("cOpenId", consumptionRecordTemp.getUserNo());
            itemTemp.put("verifyConsumeTime", consumptionRecordTemp.getVerifyConsumeTime());
            itemTemp.put("iCode", consumptionRecordTemp.getInnerCode());
            itemTemp.put("oCode", consumptionRecordTemp.getOuterCode());
            itemTemp.put("sku", consumptionRecordTemp.getSku());
            itemTemp.put("category", consumptionRecordTemp.getCategory());
            itemTemp.put("fName", consumptionRecordTemp.getFullName());

            Iterator<ConsumerBasicInfo> consumerBasicInfoIterator = consumerBasicInfoList.iterator();
            while (consumerBasicInfoIterator.hasNext()) {
                ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoIterator.next();
                if (consumptionRecordTemp.getUserNo().equals(consumerBasicInfoTemp.getOpenId())) {
                    itemTemp.put("cProvince", consumerBasicInfoTemp.getProvince());
                    itemTemp.put("cCity", consumerBasicInfoTemp.getCity());
//                    consumerBasicInfoIterator.remove();
                    break;
                }
            }

            Iterator<MaScanPoint> maScanPointIterator = maScanPointList.iterator();
            while (maScanPointIterator.hasNext()) {
                MaScanPoint maScanPointTemp = maScanPointIterator.next();
                if (consumptionRecordTemp.getInnerCode().equals(maScanPointTemp.getInnerCode())) {
                    itemTemp.put("mScanCodeTime", maScanPointTemp.getScanDate());
                    itemTemp.put("mOpenId", maScanPointTemp.getOpenId());
                    itemTemp.put("mName", maScanPointTemp.getCustormername());
                    itemTemp.put("mCategory", maScanPointTemp.getIdentityCategory());
                    itemTemp.put("mPhone", maScanPointTemp.getPhone());
                    itemTemp.put("storeId", maScanPointTemp.getStoreId());
                    itemTemp.put("sName", maScanPointTemp.getStoreName());
                    itemTemp.put("mProvince", maScanPointTemp.getProvice());
                    itemTemp.put("mCity", maScanPointTemp.getCity());
                    itemTemp.put("mArea", maScanPointTemp.getArea());
                    itemTemp.put("mTownshop", maScanPointTemp.getTownship());
                    itemTemp.put("mDAddress", maScanPointTemp.getDetailaddress());
                    itemTemp.put("mSArea", maScanPointTemp.getStorearea());

                    maScanPointIterator.remove();
                    break;
                }
            }

            /**
             * 将数据填充到result
             */



            result.add(itemTemp);

            consumptionRecordIterator.remove();

        }


        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("扫码数据");
//        设置表头
        {
            XSSFRow row = sheet.createRow(rowIndex++);

            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.DASH_DOT );
            Font font = new XSSFFont();
            font.setBold(true);
            font.setFontName("楷体");
            cellStyle.setFont(font);

            row.setRowStyle(cellStyle);

            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cell9 = row.createCell(8);


            {
//                表格占位
                XSSFCell cellt1 = row.createCell(9);
                XSSFCell cellt2 = row.createCell(10);
                XSSFCell cellt3 = row.createCell(11);
                XSSFCell cellt4 = row.createCell(12);
                XSSFCell cellt5 = row.createCell(13);
                XSSFCell cellt6 = row.createCell(14);
                XSSFCell cellt7 = row.createCell(15);

                cellt1.setCellValue("品类");
                cellt2.setCellValue("子品类");
                cellt3.setCellValue("产品描述");
                cellt4.setCellValue("Size");
                cellt5.setCellValue("片数");
                cellt6.setCellValue("箱容");
                cellt7.setCellValue("消费者对应小区");

            }


            XSSFCell cell10 = row.createCell(16);
            XSSFCell cell11 = row.createCell(17);
            XSSFCell cell12 = row.createCell(18);
            XSSFCell cell13 = row.createCell(19);
            XSSFCell cell14 = row.createCell(20);
            XSSFCell cell15 = row.createCell(21);
            XSSFCell cell16 = row.createCell(22);
            XSSFCell cell17 = row.createCell(23);
            XSSFCell cell18 = row.createCell(24);
            XSSFCell cell19 = row.createCell(25);
            XSSFCell cell20 = row.createCell(26);
            XSSFCell cell21 = row.createCell(27);
            XSSFCell cell22 = row.createCell(28);


            cell1.setCellValue("open_id");
            cell2.setCellValue( "核销时间" );
            cell3.setCellValue("袋内码");
            cell4.setCellValue("袋外码");
            cell5.setCellValue("物料编码");
            cell6.setCellValue("产品线");
            cell7.setCellValue("省份" );
            cell8.setCellValue("市");
            cell9.setCellValue("型号4位码");
            cell10.setCellValue("扫码日期");
            cell11.setCellValue( "openId");
            cell12.setCellValue( "客户姓名");
            cell13.setCellValue( "店主or店员");
            cell14.setCellValue( "手机");
            cell15.setCellValue( "店ID" );
            cell16.setCellValue( "店铺名称");
            cell17.setCellValue( "省");
            cell18.setCellValue( "市");
            cell19.setCellValue( "区");
            cell20.setCellValue( "街道");
            cell21.setCellValue( "店铺地址");
            cell22.setCellValue( "店铺对应小区");
        }


//      读取result、放入Excel
        result.forEach(i->{
            XSSFRow row = sheet.createRow(rowIndex++);
/*            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cell9 = row.createCell(8);
            XSSFCell cell10 = row.createCell(9);
            XSSFCell cell11 = row.createCell(10);
            XSSFCell cell12 = row.createCell(11);
            XSSFCell cell13 = row.createCell(12);
            XSSFCell cell14 = row.createCell(13);
            XSSFCell cell15 = row.createCell(14);
            XSSFCell cell16 = row.createCell(15);
            XSSFCell cell17 = row.createCell(16);
            XSSFCell cell18 = row.createCell(17);
            XSSFCell cell19 = row.createCell(18);
            XSSFCell cell20 = row.createCell(19);
            XSSFCell cell21 = row.createCell(20);
            XSSFCell cell22 = row.createCell(21);*/


            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cell9 = row.createCell(8);

            XSSFCell cell10 = row.createCell(16);
            XSSFCell cell11 = row.createCell(17);
            XSSFCell cell12 = row.createCell(18);
            XSSFCell cell13 = row.createCell(19);
            XSSFCell cell14 = row.createCell(20);
            XSSFCell cell15 = row.createCell(21);
            XSSFCell cell16 = row.createCell(22);
            XSSFCell cell17 = row.createCell(23);
            XSSFCell cell18 = row.createCell(24);
            XSSFCell cell19 = row.createCell(25);
            XSSFCell cell20 = row.createCell(26);
            XSSFCell cell21 = row.createCell(27);
            XSSFCell cell22 = row.createCell(28);


//          消费者核销数据


            cell1.setCellValue(i.get("cOpenId").toString());
            if (i.get("verifyConsumeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                cell2.setCellValue( (sdf.format((Date) (i.get("verifyConsumeTime")))) );
                cell2.setCellType(CellType.STRING);
            }
            cell3.setCellValue(i.get("iCode").toString());
            cell4.setCellValue(i.get("oCode").toString());
            cell5.setCellValue(i.get("sku").toString());
            cell6.setCellValue(i.get("category").toString());
            cell7.setCellValue((String) Optional.ofNullable(i.get("cProvince")).orElse("") );
            cell8.setCellValue((String) Optional.ofNullable(i.get("cCity")).orElse(""));
            cell9.setCellValue((String) i.get("fName"));

//            销售扫码数据
            if (i.get("mScanCodeTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                cell10.setCellValue(sdf.format ((Date) i.get("mScanCodeTime")));
                cell10.setCellType(CellType.STRING);
            }
            cell11.setCellValue( (String) i.get("mOpenId"));
            cell12.setCellValue( (String) i.get("mName"));
            cell13.setCellValue( (String) i.get("mCategory"));
            cell14.setCellValue( (String) i.get("mPhone"));
            if (i.get("storeId") != null) {
                cell15.setCellValue( (Integer)( i.get("storeId")) );

            }
            cell16.setCellValue( (String) i.get("sName"));
            cell17.setCellValue( (String) i.get("mProvince"));
            cell18.setCellValue( (String) i.get("mCity"));
            cell19.setCellValue( (String) i.get("mArea"));
            cell20.setCellValue( (String) i.get("mTownshop"));
            cell21.setCellValue( (String) i.get("mDAddress"));
            cell22.setCellValue( (String) i.get("mSArea"));

        });

        FileOutputStream fileOutputStream = null;
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String reportDate = simpleDateFormat.format(new Date());

            String reportLocalPath = ApplicationConfigurationSingletonFactory.getValueOfConfiguration("ReportLocalPath");

            log.info("报表存放路径："+ reportLocalPath);

            fileOutputStream = new FileOutputStream(reportLocalPath + "PlatinumBagVerifyConsumeRecord" + reportDate + ".xlsx");

            log.info("报表完整路径："+ reportLocalPath+"PlatinumBagVerifyConsumeRecord"+".xlsx");


            workbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            rowIndex = 0;
        }


    }

    /**
     * 生成白金福气袋
     */
    @Override
    public void generatePlatinumBagRedEnvelopeRecord() {

        List<Map<String,Object>> result = new LinkedList<>();

        List<PlatinumbagRedEnvelope> platinumbagRedEnvelopeList = queryDataByMutilDataSource.listAllPlatinumBagEnvelopeRecord();
        List<ConsumerBasicInfo> consumerBasicInfoList = queryDataByMutilDataSource.listAllConsumerBasicInfo();

        Map<String,ConsumerBasicInfo> consumerBasicInfoMap = consumerBasicInfoList.stream().collect(Collectors.toMap(ConsumerBasicInfo::getOpenId, i->i));



        Iterator<PlatinumbagRedEnvelope> redEnvelopeIterator = platinumbagRedEnvelopeList.iterator();
        while (redEnvelopeIterator.hasNext()) {
            Map<String,Object> itemTemp = new LinkedHashMap<>();
            PlatinumbagRedEnvelope platinumbagRedEnvelopeTemp = redEnvelopeIterator.next();

            itemTemp.put("pUserNo",   platinumbagRedEnvelopeTemp.getUserNo());
            itemTemp.put("pUserName", platinumbagRedEnvelopeTemp.getUserName());
            itemTemp.put("cPoint",    platinumbagRedEnvelopeTemp.getConsumePoint());
            itemTemp.put("prizeName", platinumbagRedEnvelopeTemp.getPrizeName());
            itemTemp.put("cTime",     platinumbagRedEnvelopeTemp.getCreateDate());
            itemTemp.put("money",     platinumbagRedEnvelopeTemp.getRewardPoint());
            itemTemp.put("isGrand",   platinumbagRedEnvelopeTemp.getIsGrant());
            itemTemp.put("lType",     platinumbagRedEnvelopeTemp.getLotteryType());
            itemTemp.put("referrer",  platinumbagRedEnvelopeTemp.getReferrerOpenid());
            itemTemp.put("serialNo",  platinumbagRedEnvelopeTemp.getSerialNo());
            itemTemp.put("lotteryId",       platinumbagRedEnvelopeTemp.getLotteryId());
//            itemTemp.put("lType",     platinumbagRedEnvelopeTemp.getLotteryType());

/*            Iterator<ConsumerBasicInfo> consumerBasicInfoIterator = consumerBasicInfoList.iterator();
            while (consumerBasicInfoIterator.hasNext()) {
                ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoIterator.next();



                if (platinumbagRedEnvelopeTemp.getUserNo().equals(consumerBasicInfoTemp.getOpenId())) {
                    ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoMap.get(platinumbagRedEnvelopeTemp.getUserNo());
                    itemTemp.put("cProvince", consumerBasicInfoTemp.getProvince());
                    itemTemp.put("cCity", consumerBasicInfoTemp.getCity());
//                    consumerBasicInfoIterator.remove();
                    break;
                }
            }*/
            ConsumerBasicInfo consumerBasicInfoTemp = consumerBasicInfoMap.get(platinumbagRedEnvelopeTemp.getUserNo());

            if (consumerBasicInfoTemp != null) {
                itemTemp.put("cProvince", Optional.ofNullable(consumerBasicInfoTemp.getProvince()).orElse("") );
                itemTemp.put("cCity", Optional.ofNullable(consumerBasicInfoTemp.getCity()).orElse(""));
            }


            if (platinumbagRedEnvelopeTemp.getLotteryType() == 40) {
//                是推荐红包的时候才去填充推荐人的信息
                ConsumerBasicInfo consumerBasicInfoTemp2 = consumerBasicInfoMap.get(platinumbagRedEnvelopeTemp.getReferrerOpenid());
                if (consumerBasicInfoTemp2 != null) {
                    itemTemp.put("rProvince", Optional.ofNullable(consumerBasicInfoTemp2.getProvince()).orElse("") );
                    itemTemp.put("rCity", Optional.ofNullable(consumerBasicInfoTemp2.getCity()).orElse(""));
                }

            }


            /**
             * 将数据填充到result
             */



            result.add(itemTemp);

            redEnvelopeIterator.remove();
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("红包发放数据");
//        设置表头
        {
            XSSFRow row = sheet.createRow(rowIndex++);

            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.DASH_DOT );
            Font font = new XSSFFont();
            font.setBold(true);
            font.setFontName("楷体");
            cellStyle.setFont(font);

            row.setRowStyle(cellStyle);

            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cellt1 = row.createCell(8);
            XSSFCell cellt2 = row.createCell(9);
            XSSFCell cellt3 = row.createCell(10);
            XSSFCell cellt4 = row.createCell(11);
            XSSFCell cellt5 = row.createCell(12);
            XSSFCell cell9 = row.createCell(13);


            cell1.setCellValue("主用户编号");
            cell2.setCellValue( "用户姓名" );
            cell3.setCellValue("消耗积分");
            cell4.setCellValue("奖品名称");
            cell5.setCellValue("抽奖时间");
            cell6.setCellValue("发放金额");
            cell7.setCellValue("发放状态" );
            cell8.setCellValue("抽奖类型");

            cellt1.setCellValue("对应被推荐消费者");
            cellt2.setCellValue("发放流水号");
            cellt3.setCellValue("红包编号");
            cellt4.setCellValue("失败描述");
            cellt5.setCellValue("省");
            cell9.setCellValue("市");


            {
//                表格占位

                XSSFCell cellt6 = row.createCell(14);
                XSSFCell cellt7 = row.createCell(15);


                cellt6.setCellValue("小区");
                cellt7.setCellValue("消费者对应小区");

            }


            XSSFCell cell10 = row.createCell(16);
            XSSFCell cell11 = row.createCell(17);
            XSSFCell cell12 = row.createCell(18);
            XSSFCell cell13 = row.createCell(19);
            cell10.setCellValue("小区");
            cell11.setCellValue( "省");
            cell12.setCellValue( "市");
            cell13.setCellValue( "小区");
/*
            XSSFCell cell14 = row.createCell(20);
            XSSFCell cell15 = row.createCell(21);
            XSSFCell cell16 = row.createCell(22);
            XSSFCell cell17 = row.createCell(23);
            XSSFCell cell18 = row.createCell(24);
            XSSFCell cell19 = row.createCell(25);
            XSSFCell cell20 = row.createCell(26);
            XSSFCell cell21 = row.createCell(27);
            XSSFCell cell22 = row.createCell(28);
*/







/*            cell14.setCellValue( "手机");
            cell15.setCellValue( "店ID" );
            cell16.setCellValue( "店铺名称");
            cell17.setCellValue( "省");
            cell18.setCellValue( "市");
            cell19.setCellValue( "区");
            cell20.setCellValue( "街道");
            cell21.setCellValue( "店铺地址");
            cell22.setCellValue( "店铺对应小区");*/
        }


//      读取result、放入Excel

        result.forEach(i->{
            XSSFRow row = sheet.createRow(rowIndex++);
/*            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cell9 = row.createCell(8);
            XSSFCell cell10 = row.createCell(9);
            XSSFCell cell11 = row.createCell(10);
            XSSFCell cell12 = row.createCell(11);
            XSSFCell cell13 = row.createCell(12);
            XSSFCell cell14 = row.createCell(13);
            XSSFCell cell15 = row.createCell(14);
            XSSFCell cell16 = row.createCell(15);
            XSSFCell cell17 = row.createCell(16);
            XSSFCell cell18 = row.createCell(17);
            XSSFCell cell19 = row.createCell(18);
            XSSFCell cell20 = row.createCell(19);
            XSSFCell cell21 = row.createCell(20);
            XSSFCell cell22 = row.createCell(21);*/


            XSSFCell cell1 = row.createCell(0);
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            XSSFCell cell5 = row.createCell(4);
            XSSFCell cell6 = row.createCell(5);
            XSSFCell cell7 = row.createCell(6);
            XSSFCell cell8 = row.createCell(7);
            XSSFCell cell9 = row.createCell(8);

            XSSFCell cell10 = row.createCell(9);
            XSSFCell cell11 = row.createCell(10);
            XSSFCell cell12 = row.createCell(12);
            XSSFCell cell13 = row.createCell(13);
            XSSFCell cell14 = row.createCell(17);
            XSSFCell cell15 = row.createCell(18);
            XSSFCell cell16 = row.createCell(19);
            XSSFCell cell17 = row.createCell(20);
/*            XSSFCell cell16 = row.createCell(22);
            XSSFCell cell17 = row.createCell(23);
            XSSFCell cell18 = row.createCell(24);
            XSSFCell cell19 = row.createCell(25);
            XSSFCell cell20 = row.createCell(26);
            XSSFCell cell21 = row.createCell(27);
            XSSFCell cell22 = row.createCell(28);*/


//          消费者核销数据


            cell1.setCellValue(i.get("pUserNo").toString());

            cell2.setCellValue(i.get("pUserName").toString());
            cell3.setCellValue(i.get("cPoint").toString());
            cell4.setCellValue(i.get("prizeName").toString());

            if (i.get("cTime") != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                cell5.setCellValue(sdf.format ((Date) i.get("cTime")));
//                cell5.setCellValue(i.get("cTime").toString());
            }


            cell6.setCellValue(i.get("money").toString());
            if (1 == ( (Integer)i.get("isGrand")) ){
                cell7.setCellValue("发放成功");
            }
            else if (2 == ( (Integer)i.get("isGrand"))){
                cell7.setCellValue("发放中");
            }
            else if (3 == ( (Integer)i.get("isGrand")) ){
                cell7.setCellValue("发放失败");
            }
            else if (0 == ( (Integer)i.get("isGrand")) ){
                cell7.setCellValue("待发放");
            }

            if (1 == ((Integer)i.get("lType")) ) {
                cell8.setCellValue("抽奖红包");

            }else if ( 40 == (Integer)i.get("lType") ) {
                cell8.setCellValue("推荐红包");
            }

            cell9.setCellValue((String) Optional.ofNullable(i.get("referrer")).orElse(""));
            cell10.setCellValue((String) i.get("serialNo"));


            cell11.setCellValue( ""+i.get("lotteryId"));

            cell12.setCellValue( (String) i.get("cProvince"));
            cell13.setCellValue( (String) i.get("cCity"));
            cell14.setCellValue( (String) i.get("rProvince"));
            cell15.setCellValue( (String) i.get("rCity"));

/*            cell16.setCellValue( (String) i.get("sName"));
            cell17.setCellValue( (String) i.get("mProvince"));
            cell18.setCellValue( (String) i.get("mCity"));
            cell19.setCellValue( (String) i.get("mArea"));
            cell20.setCellValue( (String) i.get("mTownshop"));
            cell21.setCellValue( (String) i.get("mDAddress"));
            cell22.setCellValue( (String) i.get("mSArea"));*/

        });

        FileOutputStream fileOutputStream = null;
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String reportDate = simpleDateFormat.format(new Date());

            String reportLocalPath = ApplicationConfigurationSingletonFactory.getValueOfConfiguration("ReportLocalPath");

            log.info("报表存放路径："+ reportLocalPath);

            fileOutputStream = new FileOutputStream(reportLocalPath + "PlatinumBagRedEnvelopeRecord" + reportDate + ".xlsx");

            log.info("报表完整路径："+ reportLocalPath+"PlatinumBagRedEnvelopeRecord"+".xlsx");

            workbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            rowIndex = 0;
        }



    }
}
